package co.gov.medellin.ssti.sub.model.dao.impl;

import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import co.gov.medellin.ssti.sub.model.dao.VerificacionDAO;
import co.gov.medellin.ssti.sub.model.vo.VerificacionVO;

public class VerificacionDAOImpl2 extends NamedParameterJdbcDaoSupport
		implements VerificacionDAO {

	private static final String VERIFICACION_BEN_BOT = "SELECT ben.tipo_documento TIPO_DOC_SUB, v.tipo_documento TIPO_DOC_VERI, ben.numero_documento DOCUMENTO, v.numero_documento DOCUMENTOVERI, "
			+ " ben.PRIMER_NOMBRE PRIMERNOMBRE, v.PRIMER_NOMBRE PRIMERNOMBREVERI, ben.SEGUNDO_NOMBRE SEGUNDONOMBRE, "
			+ " v.SEGUNDO_NOMBRE SEGUNDONOMBREVERI, ben.PRIMER_APELLIDO PRIMERAPELLIDO, v.PRIMER_APELLIDO PRIMERAPELLIDOVERI, "
			+ " ben.SEGUNDO_APELLIDO SEGUNDOAPELLIDO, v.SEGUNDO_APELLIDO SEGUNDOAPELLIDOVERI, ben.sexo SEXO,a.nombre DEPENDENCIA, "
			+ " p.NOMBRE PROGRAMA, sp.NOMBRE BENEFICIO, bot.VALOR_BENEFICIO VALOR, TO_CHAR(bot.FECHA_BENEFICIO, 'yyyy-mm-dd') FECHABENE, "
			+ " ben.FICHA_SISBEN FICHASISBEN, ben.HOGAR_SISBEN HOGARSISBEN, ben.PUNTAJE_SISBEN PUNTAJESISBEN, ben.NIVEL_SISBEN NIVELSISBEN, to_char(ben.FE_ENCUESTA_SISBEN, 'yyyy-mm-dd') FECHAENCUESTA, "
			+ " (SELECT nombre FROM PAOYER.tcs_valor_flexible WHERE codigo = ben.vfl_codigo_comuna) AS COMUNA, "
			+ " (SELECT nombre FROM PAOYER.tcs_valor_flexible WHERE codigo = ben.vfl_codigo_barrio) AS BARRIO, "
			+ " ben.direccion_descriptiva AS DIRECCION, "
			+ " ben.telefono AS TELEFONO, "
			+ " ben.fecha_nacimiento AS FECHANACIMIENTO "
			+ " FROM tcs_ben_beneficiario ben, tcs_ben_sub_verificacion v, tcs_ben_beneficio_otorgado bot, "
			+ " TCS_BEN_SUBPROGRAMA sp, TCS_BEN_PROGRAMA p, tcs_ben_area a "
			+ " WHERE ben.numero_documento = v.numero_documento "
			+ " AND ben.CODIGO = bot.BEN_CODIGO "
			+ " AND bot.SBPR_CODIGO = sp.CODIGO "
			+ " AND sp.PRG_CODIGO = p.CODIGO "
			+ " AND p.AREA_CODIGO = a.CODIGO AND v.USUARIO_CREA = :USUARIO_CREA ";

	private static final String VERIFICACION_SISBEN = "SELECT sis.tipodoc, "
			+ " v.tipo_documento TIPO_DOC_VERI, "
			+ " sis.document, "
			+ " v.numero_documento DOCUMENTOVERI, "
			+ " sis.NOM1, v.PRIMER_NOMBRE PRIMERNOMBREVERI, "
			+ " sis.NOM2, v.SEGUNDO_NOMBRE SEGUNDONOMBREVERI, "
			+ " sis.APE1, v.PRIMER_APELLIDO PRIMERAPELLIDOVERI, "
			+ " sis.APE2, v.SEGUNDO_APELLIDO SEGUNDOAPELLIDOVERI, "
			+ " sis.sexo, sis.fechanto, sis.TELES, "
			+ " sis.DIRECC, sis.barrio, sis.zona, sis.comuna, "
			+ " sis.estcivil, sis.carnet, sis.puntaje, sis.ficha, sis.fechaingre, "
			+ " sis.fechamodp, sis.parentes, sis.tpersona, "
			+ " sis.hogar, sis.thogar, sis.orden, sis.asiste, sis.tipoesta, "
			+ " sis.grado, sis.nivel, sis.jefeug, sis.unigasto, sis.nombarrio, "
			+ " sis.vereda, sis.nomvereda "
			+ " FROM tcs_ben_sub_verificacion v "
			+ " JOIN tcs_ben_sisben3_new_2012 sis "
			+ " ON v.numero_documento = sis.document "
			+ " WHERE v.USUARIO_CREA = :USUARIO_CREA";

	private static final String VERIFICACION_BEN = "SELECT ben.tipo_documento, "
			+ " v.tipo_documento TIPO_DOC_VERI, "
			+ " ben.numero_documento, "
			+ " v.numero_documento DOCUMENTOVERI, "
			+ " ben.primer_nombre, v.PRIMER_NOMBRE PRIMERNOMBREVERI, "
			+ " ben.segundo_nombre, v.SEGUNDO_NOMBRE SEGUNDONOMBREVERI, "
			+ " ben.primer_apellido, v.PRIMER_APELLIDO PRIMERAPELLIDOVERI, "
			+ " ben.segundo_apellido, v.SEGUNDO_APELLIDO SEGUNDOAPELLIDOVERI, "
			+ " ben.fecha_nacimiento, ben.sexo, ben.telefono, "
			+ " ben.direccion_descriptiva, "
			+ " (SELECT nombre FROM PAOYER.tcs_valor_flexible WHERE codigo = ben.vfl_codigo_comuna) AS COMUNA, "
			+ " (SELECT nombre FROM PAOYER.tcs_valor_flexible WHERE codigo = ben.vfl_codigo_barrio) AS BARRIO, "
			+ " (SELECT nombre FROM PAOYER.tcs_valor_flexible WHERE codigo = ben.vfl_codigo_zona) AS ZONA, "
			+ " ben.correo_electronico, ben.etnia, "
			+ " ben.FICHA_SISBEN FICHASISBEN, ben.HOGAR_SISBEN HOGARSISBEN, ben.PUNTAJE_SISBEN PUNTAJESISBEN, "
			+ " ben.NIVEL_SISBEN NIVELSISBEN, to_char(ben.FE_ENCUESTA_SISBEN, 'yyyy-mm-dd') FECHAENCUESTA "
			+ " FROM tcs_ben_sub_verificacion v "
			+ " JOIN tcs_ben_beneficiario ben "
			+ " ON v.numero_documento = ben.numero_documento "
			+ " WHERE v.USUARIO_CREA = :USUARIO_CREA";

	// private static final String P_POBLA_DATOS_DE_CLOB_VERI =
	// "{call beneficios.p_pobla_datos_de_clob_veri(:lote, :usuario)}";

	private static final String P_POBLA_DATOS_DE_CLOB_VERI = "P_POBLA_DATOS_DE_CLOB_VERI";

	private static final String DELETE_FROM_TCS_BEN_SUB_VERIFICACION_BY_USUARIO_CREA = "DELETE FROM TCS_BEN_SUB_VERIFICACION "
			+ " WHERE USUARIO_CREA = :USUARIO_CREA ";

	public void uploadFile(byte[] fileData, String nomUser)
			throws UnsupportedEncodingException {

		String strData = new String(fileData, "ISO-8859-1");

		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(getDataSource())
				.withProcedureName(P_POBLA_DATOS_DE_CLOB_VERI)
				.declareParameters(new SqlParameter("lote", Types.CLOB))
				.declareParameters(new SqlParameter("usuario", Types.VARCHAR))
				.declareParameters(
						new SqlParameter("pv_separador", Types.VARCHAR));

		SqlParameterSource paramMap = new MapSqlParameterSource()
				.addValue("lote", strData, Types.CLOB)
				.addValue("usuario", nomUser, Types.VARCHAR)
				.addValue("pv_separador", ";", Types.VARCHAR);

		simpleJdbcCall.execute(paramMap);
	}

	public int deleteRecordsOld(String nomUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		namedParameters.put("USUARIO_CREA", nomUser);
		return getNamedParameterJdbcTemplate().update(
				DELETE_FROM_TCS_BEN_SUB_VERIFICACION_BY_USUARIO_CREA,
				namedParameters);
	}

	public Collection<VerificacionVO> findVerificationBeneficiarioBeneficio(
			String nomUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		namedParameters.put("USUARIO_CREA", nomUser);

		System.out.println("query: " + VERIFICACION_BEN_BOT);
		System.out.println("usuario_crea: " + nomUser);

		List<VerificacionVO> verificados = getNamedParameterJdbcTemplate()
				.query(VERIFICACION_BEN_BOT, namedParameters,
						new RowMapper<VerificacionVO>() {

							public VerificacionVO mapRow(ResultSet rs,
									int rowNum) throws SQLException {
								VerificacionVO vo = new VerificacionVO();
								vo.setTipoDocSub(rs.getString("TIPO_DOC_SUB"));
								vo.setTipoDocVeri(rs.getString("TIPO_DOC_VERI"));
								vo.setNumeroDocumento(rs.getString("DOCUMENTO"));
								vo.setNumeroDocumentoVeri(rs
										.getString("DOCUMENTOVERI"));
								vo.setPrimerNombre(rs.getString("PRIMERNOMBRE"));
								vo.setPrimerNombreVeri(rs
										.getString("PRIMERNOMBREVERI"));
								vo.setSegundoNombre(rs
										.getString("SEGUNDONOMBRE"));
								vo.setSegundoNombreVeri(rs
										.getString("SEGUNDONOMBREVERI"));
								vo.setPrimerApellido(rs
										.getString("PRIMERAPELLIDO"));
								vo.setPrimerApellidoVeri(rs
										.getString("PRIMERAPELLIDOVERI"));
								vo.setSegundoApellido(rs
										.getString("SEGUNDOAPELLIDO"));
								vo.setSegundoApellidoVeri(rs
										.getString("SEGUNDOAPELLIDOVERI"));
								vo.setSexo(rs.getString("SEXO"));
								vo.setComuna(rs.getString("COMUNA"));
								vo.setBarrio(rs.getString("BARRIO"));
								vo.setDireccion(rs.getString("DIRECCION"));
								vo.setTelefono(rs.getString("TELEFONO"));
								vo.setFechaNacimiento(rs
										.getDate("FECHANACIMIENTO"));
								vo.setDependencia(rs.getString("DEPENDENCIA"));
								vo.setPrograma(rs.getString("PROGRAMA"));
								vo.setBeneficio(rs.getString("BENEFICIO"));
								vo.setValorBeneficio(rs.getLong("VALOR"));
								vo.setFechaBeneficio(rs.getString("FECHABENE"));
								vo.setFichaSisben(rs.getString("FICHASISBEN"));
								vo.setHogarSisben(rs.getString("HOGARSISBEN"));
								vo.setPuntajeSisben(rs
										.getString("PUNTAJESISBEN"));
								vo.setNivelSisben(rs.getInt("NIVELSISBEN"));
								vo.setFechaSisben(rs.getString("FECHAENCUESTA"));
								return vo;
							}
						});

		return verificados;
	}

	@Override
	public Collection<VerificacionVO> findVerificationSisben(String nomUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		namedParameters.put("USUARIO_CREA", nomUser);

		List<VerificacionVO> verificados = getNamedParameterJdbcTemplate()
				.query(VERIFICACION_SISBEN, namedParameters,
						new RowMapper<VerificacionVO>() {

							public VerificacionVO mapRow(ResultSet rs,
									int rowNum) throws SQLException {
								VerificacionVO vo = new VerificacionVO();
								vo.getBeneficiarioSisben().setTipoDocumento(
										rs.getString("TIPODOC"));
								vo.setTipoDocVeri(rs.getString("TIPO_DOC_VERI"));
								vo.getBeneficiarioSisben().setNumeroDocumento(
										rs.getString("DOCUMENT"));
								vo.setNumeroDocumentoVeri(rs
										.getString("DOCUMENTOVERI"));
								vo.getBeneficiarioSisben().setPrimerNombre(
										rs.getString("NOM1"));
								vo.setPrimerNombreVeri(rs
										.getString("PRIMERNOMBREVERI"));
								vo.getBeneficiarioSisben().setSegundoNombre(
										rs.getString("NOM2"));
								vo.setSegundoNombreVeri(rs
										.getString("SEGUNDONOMBREVERI"));
								vo.getBeneficiarioSisben().setPrimerApellido(
										rs.getString("APE1"));
								vo.setPrimerApellidoVeri(rs
										.getString("PRIMERAPELLIDOVERI"));
								vo.getBeneficiarioSisben().setSegundoApellido(
										rs.getString("APE2"));
								vo.setSegundoApellidoVeri(rs
										.getString("SEGUNDOAPELLIDOVERI"));
								vo.getBeneficiarioSisben().setSexo(
										rs.getString("SEXO"));
								vo.getBeneficiarioSisben().setFechaNacimiento(
										rs.getString("FECHANTO"));
								vo.getBeneficiarioSisben().setTelefono(
										rs.getString("TELES"));
								vo.getBeneficiarioSisben().setDireccion(
										rs.getString("DIRECC"));
								vo.getBeneficiarioSisben().setBarrio(
										rs.getString("BARRIO"));
								vo.getBeneficiarioSisben().setZona(
										rs.getString("ZONA"));
								vo.getBeneficiarioSisben().setComuna(
										rs.getString("COMUNA"));
								vo.getBeneficiarioSisben().setEstadoCivil(
										rs.getString("ESTCIVIL"));
								vo.getBeneficiarioSisben().setCarnet(
										rs.getString("CARNET"));
								vo.getBeneficiarioSisben().setPuntaje(
										rs.getString("PUNTAJE"));
								vo.getBeneficiarioSisben().setFicha(
										rs.getString("FICHA"));
								vo.getBeneficiarioSisben().setFechaIngreso(
										rs.getString("FECHAINGRE"));
								vo.getBeneficiarioSisben()
										.setFechaModificacion(
												rs.getString("FECHAMODP"));
								vo.getBeneficiarioSisben().setParentesco(
										rs.getString("PARENTES"));
								vo.getBeneficiarioSisben().setTpersona(
										rs.getString("TPERSONA"));
								vo.getBeneficiarioSisben().setHogar(
										rs.getString("HOGAR"));
								vo.getBeneficiarioSisben().setThogar(
										rs.getString("THOGAR"));
								vo.getBeneficiarioSisben().setOrden(
										rs.getString("ORDEN"));
								vo.getBeneficiarioSisben().setAsiste(
										rs.getString("ASISTE"));
								vo.getBeneficiarioSisben().setTipoEstado(
										rs.getString("TIPOESTA"));
								vo.getBeneficiarioSisben().setGrado(
										rs.getString("GRADO"));
								vo.getBeneficiarioSisben().setNivel(
										rs.getString("NIVEL"));
								vo.getBeneficiarioSisben().setJefeug(
										rs.getString("JEFEUG"));
								vo.getBeneficiarioSisben().setUnigasto(
										rs.getString("UNIGASTO"));
								vo.getBeneficiarioSisben().setNombreBarrio(
										rs.getString("NOMBARRIO"));
								vo.getBeneficiarioSisben().setVereda(
										rs.getString("VEREDA"));
								vo.getBeneficiarioSisben().setNombreVereda(
										rs.getString("NOMVEREDA"));

								return vo;
							}
						});

		return verificados;
	}

	@Override
	public Collection<VerificacionVO> findVerificationBeneficiario(
			String nomUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		namedParameters.put("USUARIO_CREA", nomUser);
		System.out.println("query: " + VERIFICACION_BEN);
		System.out.println("usuario_crea:" + nomUser);
		List<VerificacionVO> verificados = getNamedParameterJdbcTemplate()
				.query(VERIFICACION_BEN, namedParameters,
						new RowMapper<VerificacionVO>() {

							public VerificacionVO mapRow(ResultSet rs,
									int rowNum) throws SQLException {

								VerificacionVO vo = new VerificacionVO();
								vo.setTipoDocSub(rs.getString("TIPO_DOCUMENTO"));
								vo.setTipoDocVeri(rs.getString("TIPO_DOC_VERI"));
								vo.setNumeroDocumento(rs
										.getString("NUMERO_DOCUMENTO"));
								vo.setNumeroDocumentoVeri(rs
										.getString("DOCUMENTOVERI"));
								vo.setPrimerNombre(rs
										.getString("PRIMER_NOMBRE"));
								vo.setPrimerNombreVeri(rs
										.getString("PRIMERNOMBREVERI"));
								vo.setSegundoNombre(rs
										.getString("SEGUNDO_NOMBRE"));
								vo.setSegundoNombreVeri(rs
										.getString("SEGUNDONOMBREVERI"));
								vo.setPrimerApellido(rs
										.getString("PRIMER_APELLIDO"));
								vo.setPrimerApellidoVeri(rs
										.getString("PRIMERAPELLIDOVERI"));
								vo.setSegundoApellido(rs
										.getString("SEGUNDO_APELLIDO"));
								vo.setSegundoApellidoVeri(rs
										.getString("SEGUNDOAPELLIDOVERI"));
								vo.setFechaNacimiento(rs
										.getDate("FECHA_NACIMIENTO"));
								vo.setSexo(rs.getString("SEXO"));
								vo.setTelefono(rs.getString("TELEFONO"));
								vo.setDireccion(rs
										.getString("DIRECCION_DESCRIPTIVA"));
								vo.setBarrio(rs.getString("BARRIO"));
								vo.setComuna(rs.getString("COMUNA"));
								vo.setZona(rs.getString("ZONA"));
								vo.setCorreoElectronico(rs
										.getString("CORREO_ELECTRONICO"));
								vo.setEtnia(rs.getString("ETNIA"));
								vo.setFichaSisben(rs.getString("FICHASISBEN"));
								vo.setHogarSisben(rs.getString("HOGARSISBEN"));
								vo.setPuntajeSisben(rs
										.getString("PUNTAJESISBEN"));
								vo.setNivelSisben(rs.getInt("NIVELSISBEN"));
								vo.setFechaSisben(rs.getString("FECHAENCUESTA"));
								return vo;
							}
						});

		return verificados;
	}
}
